package com.manli.timing.service;


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.manli.timing.dao.*;
import com.manli.timing.dto.OrderInfo;
import com.manli.timing.dto.TaobaokeOrders;
import com.manli.timing.dto.ViewOrderGoods;
import com.manli.timing.pojo.*;
import com.manli.timing.utils.DingDanXiaUtils;
import com.manli.timing.utils.WordUtil;
import com.manli.wechat.utils.wxpay.WxPayPojoV2;
import com.manli.wechat.utils.wxpay.config.MyWxPayConfig;
import com.manli.wechat.utils.wxpay.sdk.WXPay;
import com.manli.wechat.utils.wxpayv3.pojo.ActivationCouponBatch;
import com.manli.wechat.utils.wxpayv3.wxsdk.WxpayV3Utils;
import com.sun.glass.ui.View;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class TimingService {
    Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    TaobaokeOrdersDao taobaokeOrdersDao;
    @Autowired
    private DingDanXiaUtils dingDanXiaUtils;
    @Autowired
    private ViewOrderGoodsDao viewOrderGoodsDao;
    @Autowired
    private SQLManager sqlManager;
    @Autowired
    private OrderInfoDao orderInfoDao;
    @Autowired
    private GoodsEvainfoDao goodsEvainfoDao;
    @Autowired
    private WechatActivationCouponBatchDao wechatActivationCouponBatchDao;

    public String dingdanxiaKey = "DcfeAj4wyZ875h32YYggeWr1kODgSbf1";

    @Value("${wechatpay.certPath}")
    String certPath ;
    //漫狸科技（温州）服务商appid
    String appID = "wx6f56d14c68184b2f";
    //漫狸科技（温州） 漫淘淘小程序
    String appIdMiniprogram = "wx68b41af749849d4e";
    //漫狸科技（温州） 服务商商户号
    String mchID = "1601420795";
    String key = "manli111111111111111111111111111";
    @Value("${wechatpay.notifyUrl}")
    String notify_url ;
    @Value("${wechatpay.refuseNotifyUrl}")
    String refuse_notify_url ;
    //漫狸科技工作室商户号
    String sub_mch_id = "1601704787";

    public void getTaoBaoKeOrders(String page, String startTime, String endTime) throws Exception{
        log.info("开始获取已经结算的淘宝客单子");
        TklOrderQuery tklOrderQuery = new TklOrderQuery(dingdanxiaKey,startTime,endTime,"",page);//淘客订单状态，12-付款，13-关闭，14-确认收货，3-结算成功;不传，表示所有状态
        dingDanXiaUtils.queryOrdes(tklOrderQuery);

    }

    public void getTaoBaoKeOrdersStatus() throws Exception{
        //查询还未结算的订单
        List<TaobaokeOrders> list = taobaokeOrdersDao.execute("select * from taobaoke_orders where tk_status != ?","3");
        for(int i=0;i<list.size();i++){
            TklOrderQuery tklOrderQuery = new TklOrderQuery(dingdanxiaKey,list.get(i).getQStartTime(),list.get(i).getQEndTime(),"","1");//淘客订单状态，12-付款，13-关闭，14-确认收货，3-结算成功;不传，表示所有状态
            dingDanXiaUtils.queryOrdesStatus(tklOrderQuery);
        }
    }

    public void changeOrderInfoStatus() {
        List<ViewOrderGoods> list = viewOrderGoodsDao.execute(
                "" +
                " SELECT " +
                " order_info_id," +
                " pay_time " +
                " FROM "+
                " view_order_goods " +
                "WHERE 1=1 " +
                "and `status` = 1 " +
                "AND TIMESTAMPDIFF(DAY, pay_time, now()) > 7");
        for (ViewOrderGoods viewOrderGoods: list) {
            OrderInfo orderInfo = new OrderInfo();
            orderInfo.setOrderInfoId(viewOrderGoods.getOrderInfoId());
            orderInfo.setStatus(4);
            orderInfoDao.updateTemplateById(orderInfo);
        }
    }

    /**
     * 定时查询已经支付的一元拍卖的预支付订单
     */
    public void refuseOneYuanNotMostPrice()  {
        List<Map> list = sqlManager.execute(new SQLReady("" +
                "SELECT " +
                " max(al.activity_price) maxActivityPrice," +
                " al.activity_type_id," +
                " al.goods_def_id," +
                " al.goods_model_id," +
                " al.player_openid," +
                " al.player_unionid," +
                " al.status," +
                " ag.activity_end_time " +
                "FROM " +
                " `activity_log` al " +
                "LEFT JOIN activity_goods ag ON al.activity_type_id = ag.activity_type_id " +
                "AND al.goods_def_id = ag.goods_def_id  " +
                "where ag.activity_end_time < now() " +
                "and ag.activity_end_time > DATE_SUB(now(),INTERVAL 1 DAY) " +
                "GROUP BY " +
                " al.goods_def_id, " +
                " al.goods_model_id " +
                "HAVING " +
                " al.activity_type_id = 3 " +
                "AND al.STATUS = 1 "), Map.class);

        for (Map map: list) {
            String playUnionid = (String)map.get("playerUnionid");
            Integer goodsDefId = (Integer)map.get("goodsDefId");
            Integer goodsModelId = (Integer)map.get("goodsModelId");

            List<OrderInfo> orderInfos = sqlManager.execute(new SQLReady("" +
                    "select * from order_info " +
                    "where activity_type_id = 3 " +
                    "and goods_def_id = ? " +
                    "and goods_model_id = ? " +
                    "and buyer_unionid != ? " +
                    "and should_pay_price = 1 " +
                    "and status = 1 " , goodsDefId , goodsModelId , playUnionid) , OrderInfo.class);
            for (OrderInfo orderInfo: orderInfos) {
                //退款
                try {
                    refuseOrderInfoSendToWechat(orderInfo);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public void refuseOrderInfoSendToWechat(OrderInfo orderInfo) throws Exception {
        /*String certPath = "D:\\weixinpayca\\WXCertUtil\\cert\\1601420795_20200806_cert\\apiclient_cert.p12";
        //漫狸科技（温州）服务商appid
        String appID = "wx6f56d14c68184b2f";
        //漫狸科技（温州） 漫淘淘小程序
        String appIdMiniprogram = "wx68b41af749849d4e";
        //漫狸科技（温州） 服务商商户号
        String mchID = "1601420795";
        String key = "manli111111111111111111111111111";
        String notify_url = "https://manli.mynatapp.cc/manli/wechatpay/orderPayNotifyo";
        String refuse_notify_url = "https://manli.mynatapp.cc/manli/wechatpay/orderRefuseNotify";
        //漫狸科技工作室商户号
        String sub_mch_id = "1601704787";*/

        MyWxPayConfig config = new MyWxPayConfig(certPath, appID, mchID, key);
        WXPay wxpay = new WXPay(config);
        WxPayPojoV2 wxPayPojoV2 = new WxPayPojoV2();
        wxPayPojoV2.setAppid(appID);
        wxPayPojoV2.setMch_id(mchID);
        wxPayPojoV2.setSub_mch_id(orderInfo.getPayMchId());
        wxPayPojoV2.setOut_trade_no(orderInfo.getOrderNoMain());
        //计算订单总金额
        String total_fee = new BigDecimal(String.valueOf(orderInfo.getPayPrice()))
                .add(new BigDecimal(String.valueOf(orderInfo.getPreferentialPrice())))
                .multiply(new BigDecimal("100")).stripTrailingZeros().toPlainString();
        wxPayPojoV2.setTotal_fee(total_fee);
        wxPayPojoV2.setOut_refund_no(orderInfo.getOrderNoSub());
        String refund_fee = null ;
        if(orderInfo.getSource().indexOf("phone")>-1){
            refund_fee = new BigDecimal(String.valueOf(orderInfo.getGoodsModelSalePrice()))
                    .multiply(new BigDecimal(String.valueOf(orderInfo.getBuyCount())))
                    .multiply(new BigDecimal("100")).stripTrailingZeros().toPlainString();
        }else{
            refund_fee = new BigDecimal(String.valueOf(orderInfo.getGoodsModelChannelPrice()))
                    .multiply(new BigDecimal(String.valueOf(orderInfo.getBuyCount())))
                    .multiply(new BigDecimal("100")).stripTrailingZeros().toPlainString();
        }

        wxPayPojoV2.setRefund_fee(refund_fee);
        //wxPayPojoV2.setRefund_desc("售完");
        wxPayPojoV2.setNotify_url(refuse_notify_url);

        Map map = JSON.parseObject(JSON.toJSONString(wxPayPojoV2), Map.class);
        Map<String, String> resp = wxpay.refund(map);
        logger.info(resp.toString());
        if(resp.get("return_code").equals("SUCCESS")){
            if(!resp.get("result_code").equals("SUCCESS")){
                logger.error(resp.get("err_code_des").toString());
                throw new Exception(resp.get("err_code_des").toString());
            }
        }else{
            logger.error(resp.get("return_msg").toString());
            throw new Exception(resp.get("return_msg").toString());
        }
    }

    public void statisticsAppraiseInfo() {
        //查询30天内商品成交数量
        String sql = "select count(*) count_num,goods_def_id from order_info where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time) and status = 4 group by goods_def_id";
        SQLReady sqlReady = new SQLReady(sql);
        List<GoodsSuccess> goodsSuccessList = sqlManager.execute(sqlReady, GoodsSuccess.class);
        //更新或新增商品统计
        goodsSuccessList.forEach(goodsSuccess -> {
            List<GoodsEvainfo> goodsEvainfoList = goodsEvainfoDao.createQuery().andEq("goods_def_id",goodsSuccess.getGoodsDefId()).select();
            if(goodsEvainfoList.size() > 0){
                //已存在该商品的统计记录
                GoodsEvainfo goodsEvainfo = new GoodsEvainfo();
                goodsEvainfo.setId(goodsEvainfoList.get(0).getId());
                goodsEvainfo.setMonthTradeCount(goodsSuccess.getCountNum());
                goodsEvainfoDao.updateTemplateById(goodsEvainfo);
            }else{
                //不存在该商品的统计记录，新增
                GoodsEvainfo goodsEvainfo = new GoodsEvainfo();
                goodsEvainfo.setGoodsDefId(goodsSuccess.getGoodsDefId());
                goodsEvainfo.setMonthTradeCount(goodsSuccess.getCountNum() == null?0:goodsSuccess.getCountNum());
                goodsEvainfo.setUpdateTime(new Date());
                goodsEvainfoDao.insert(goodsEvainfo);
            }
        });
        //更新商品成交数量完毕

        //查询商品的好评数量，中评数量，差评数量，平均分
        goodsSuccessList.forEach(goodsSuccess -> {
            String sql1 = "SELECT sum( CASE WHEN appraise_score >= 8.5 THEN 1 ELSE 0 END ) good_count, " +
                    "  sum( CASE WHEN appraise_score >= 6 AND appraise_score < 8.5 THEN 1 ELSE 0 END ) normal_count, " +
                    "  sum( CASE WHEN appraise_score < 6 THEN 1 ELSE 0 END ) bad_count, " +
                    "  round( avg( appraise_score ), 2 ) avg_score  " +
                    " FROM " +
                    "  appraise_log  " +
                    "WHERE " +
                    " goods_def_id = ?";
            SQLReady sqlReady1 = new SQLReady(sql1,goodsSuccess.getGoodsDefId());
            List<GoodsAppraise> goodsAppraiseList = sqlManager.execute(sqlReady1, GoodsAppraise.class);

            List<GoodsEvainfo> goodsEvainfoList = goodsEvainfoDao.createQuery().andEq("goods_def_id",goodsSuccess.getGoodsDefId()).select();
            if(goodsAppraiseList.size() > 0){
                //已存在该商品的统计记录
                GoodsEvainfo goodsEvainfo = new GoodsEvainfo();
                goodsEvainfo.setId(goodsEvainfoList.get(0).getId());
                goodsEvainfo.setGoodTradeCount(goodsAppraiseList.get(0).getGoodCount() == null?0:goodsAppraiseList.get(0).getGoodCount());
                goodsEvainfo.setNormalTradeConut(goodsAppraiseList.get(0).getNormalCount() == null?0:goodsAppraiseList.get(0).getNormalCount());
                goodsEvainfo.setBadTradeCount(goodsAppraiseList.get(0).getBadCount() == null?0:goodsAppraiseList.get(0).getBadCount());
                goodsEvainfo.setScore(goodsAppraiseList.get(0).getAvgScore() == null?0D:goodsAppraiseList.get(0).getAvgScore());
                goodsEvainfo.setUpdateTime(new Date());
                goodsEvainfoDao.updateTemplateById(goodsEvainfo);
            }
        });
        //更新商品好评数量，中评数量，差评数量，平均分完毕

        //商品标签统计
        //先统计最新一个月内的
        goodsSuccessList.forEach(goodsSuccess -> {
            String sql1 = "select GROUP_CONCAT(appraise_msg) from appraise_log where goods_def_id = ? and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_time)";
            SQLReady sqlReady1 = new SQLReady(sql1,goodsSuccess.getGoodsDefId());
            List<String> stringList = sqlManager.execute(sqlReady1, String.class);

            String sql5 = "select * from goods_evainfo where goods_def_id = ?";
            SQLReady sqlReady5 = new SQLReady(sql5,goodsSuccess.getGoodsDefId());
            List<GoodsEvainfo> goodsEvainfoList = sqlManager.execute(sqlReady5, GoodsEvainfo.class);
            if(stringList.size() > 0){
                if(!StringUtils.isEmpty(stringList.get(0))){
                    List<String> labels = WordUtil.getGoodsLabels(stringList.get(0));
                    if(labels.size() >= 3){
                        GoodsEvainfo goodsEvainfo = new GoodsEvainfo();
                        goodsEvainfo.setId(goodsEvainfoList.get(0).getId());
                        goodsEvainfo.setLabel1(labels.get(0));
                        String sql2 = "select count(*) from appraise_log where goods_def_id = ? and appraise_msg like '%" + labels.get(0) + "%'";
                        SQLReady sqlReady2 = new SQLReady(sql2,goodsSuccess.getGoodsDefId());
                        List<Integer> count1 = sqlManager.execute(sqlReady2, Integer.class);
                        goodsEvainfo.setLabel1Count(count1.get(0) == null?1:count1.get(0));

                        goodsEvainfo.setLabel2(labels.get(1));
                        String sql3 = "select count(*) from appraise_log where goods_def_id = ? and appraise_msg like '%" + labels.get(1) + "%'";
                        SQLReady sqlReady3 = new SQLReady(sql3,goodsSuccess.getGoodsDefId());
                        List<Integer> count2 = sqlManager.execute(sqlReady3, Integer.class);
                        goodsEvainfo.setLabel2Count(count2.get(0) == null?1:count2.get(0));

                        goodsEvainfo.setLabel3(labels.get(2));
                        String sql4 = "select count(*) from appraise_log where goods_def_id = ? and appraise_msg like '%" + labels.get(2) + "%'";
                        SQLReady sqlReady4 = new SQLReady(sql4,goodsSuccess.getGoodsDefId());
                        List<Integer> count3 = sqlManager.execute(sqlReady4, Integer.class);
                        goodsEvainfo.setLabel3Count(count3.get(0) == null?1:count3.get(0));

                        goodsEvainfo.setUpdateTime(new Date());
                        goodsEvainfoDao.updateTemplateById(goodsEvainfo);

                    }
                    if(labels.size() == 2){
                        GoodsEvainfo goodsEvainfo = new GoodsEvainfo();
                        goodsEvainfo.setId(goodsEvainfoList.get(0).getId());
                        goodsEvainfo.setLabel1(labels.get(0));
                        String sql2 = "select count(*) from appraise_log where goods_def_id = ? and appraise_msg like '%" + labels.get(0) + "%'";
                        SQLReady sqlReady2 = new SQLReady(sql2,goodsSuccess.getGoodsDefId());
                        List<Integer> count1 = sqlManager.execute(sqlReady2, Integer.class);
                        goodsEvainfo.setLabel1Count(count1.get(0) == null?1:count1.get(0));

                        goodsEvainfo.setLabel2(labels.get(1));
                        String sql3 = "select count(*) from appraise_log where goods_def_id = ? and appraise_msg like '%" + labels.get(1) + "%'";
                        SQLReady sqlReady3 = new SQLReady(sql3,goodsSuccess.getGoodsDefId());
                        List<Integer> count2 = sqlManager.execute(sqlReady3, Integer.class);
                        goodsEvainfo.setLabel2Count(count2.get(0) == null?1:count2.get(0));

                        goodsEvainfo.setUpdateTime(new Date());
                        goodsEvainfoDao.updateTemplateById(goodsEvainfo);

                    }
                    if(labels.size() == 2){
                        GoodsEvainfo goodsEvainfo = new GoodsEvainfo();
                        goodsEvainfo.setId(goodsEvainfoList.get(0).getId());
                        goodsEvainfo.setLabel1(labels.get(0));
                        String sql2 = "select count(*) from appraise_log where goods_def_id = ? and appraise_msg like '%" + labels.get(0) + "%'";
                        SQLReady sqlReady2 = new SQLReady(sql2,goodsSuccess.getGoodsDefId());
                        List<Integer> count1 = sqlManager.execute(sqlReady2, Integer.class);
                        goodsEvainfo.setLabel1Count(count1.get(0) == null?1:count1.get(0));

                        goodsEvainfo.setLabel2(labels.get(1));
                        String sql3 = "select count(*) from appraise_log where goods_def_id = ? and appraise_msg like '%" + labels.get(1) + "%'";
                        SQLReady sqlReady3 = new SQLReady(sql3,goodsSuccess.getGoodsDefId());
                        List<Integer> count2 = sqlManager.execute(sqlReady3, Integer.class);
                        goodsEvainfo.setLabel2Count(count2.get(0) == null?1:count2.get(0));

                        goodsEvainfo.setUpdateTime(new Date());
                        goodsEvainfoDao.updateTemplateById(goodsEvainfo);

                    }
                    if(labels.size() == 1){
                        GoodsEvainfo goodsEvainfo = new GoodsEvainfo();
                        goodsEvainfo.setId(goodsEvainfoList.get(0).getId());
                        goodsEvainfo.setLabel1(labels.get(0));
                        String sql2 = "select count(*) from appraise_log where goods_def_id = ? and appraise_msg like '%" + labels.get(0) + "%'";
                        SQLReady sqlReady2 = new SQLReady(sql2,goodsSuccess.getGoodsDefId());
                        List<Integer> count1 = sqlManager.execute(sqlReady2, Integer.class);
                        goodsEvainfo.setLabel1Count(count1.get(0) == null?1:count1.get(0));

                        goodsEvainfo.setUpdateTime(new Date());
                        goodsEvainfoDao.updateTemplateById(goodsEvainfo);
                    }
                }
            }
        });
    }

    public void refusePingTuanNoSuccess() throws Exception {
        List<Map> list = sqlManager.execute(new SQLReady("" +
                "SELECT " +
                " al.goods_def_id, " +
                " al.goods_model_id, " +
                " al.sponsor_unionid, " +
                " al.activity_type_id, " +
                " count(1) personNum " +
                "FROM " +
                " activity_log al " +
                "LEFT JOIN activity_goods ag ON al.activity_type_id = ag.activity_type_id " +
                "AND al.goods_def_id = ag.goods_def_id " +
                "WHERE " +
                " al.activity_type_id = 2 " +
                "AND ag.activity_end_time < now() " +
                "AND ag.activity_end_time > DATE_SUB(now(), INTERVAL 1 DAY) " +
                "GROUP BY " +
                " al.sponsor_unionid, " +
                " al.goods_def_id, " +
                " al.goods_model_id having count(1) < 4 "), Map.class);

        for (Map map: list) {
            //查询有哪些团
            String sponsorUnionid = (String) map.get("sponsorUnionid");
            Integer goodsDefId = (Integer) map.get("goodsDefId");
            Integer goodsModelId = (Integer) map.get("goodsModelId");
            //获取这个团的所有人员
            List<Map> listTuan = sqlManager.execute(new SQLReady("" +
                    "select player_openid from activity_log " +
                    "where sponsor_unionid = ? " +
                    "and goods_def_id=? " +
                    "and goods_model_id = ? " +
                    "and activity_type_id = ?", sponsorUnionid, goodsDefId, goodsModelId, 2), Map.class);

            for (Map tuanMap : listTuan) {
                String playerOpenid = (String) tuanMap.get("playerOpenid");
                //查询这个人的订单信息
                OrderInfo orderInfo = orderInfoDao.createLambdaQuery()
                        .andEq("goods_def_id", goodsDefId)
                        .andEq("goods_model_id", goodsModelId)
                        .andEq("buyer_openid", playerOpenid)
                        .andEq("activity_type_id", 2)
                        .andEq("status" , 1).single();
                //发起退款
                try {
                    if(orderInfo!=null){
                        refuseOrderInfoSendToWechat(orderInfo);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Transactional
    public void activeBranchWeChatCoupons() throws Exception{
        List<WechatActivationCouponBatch> batches = wechatActivationCouponBatchDao.execute("select * from wechat_activation_coupon_batch where status = 0");
        for(int i=0;i<batches.size();i++){
            String stockId = batches.get(i).getStockId();
            ActivationCouponBatch activationCouponBatch = new ActivationCouponBatch();
            activationCouponBatch.setStock_creator_mchid("1601420795");
            JSONObject jsonObject1 = JSONObject.parseObject(WxpayV3Utils.activationCouponBatch(activationCouponBatch,stockId,"1601420795"));
            if(jsonObject1.get("stock_id") != null){
                //更新批次状态
                WechatActivationCouponBatch wechatActivationCouponBatch = new WechatActivationCouponBatch();
                wechatActivationCouponBatch.setId(batches.get(i).getId());
                wechatActivationCouponBatch.setStatus(1);
                wechatActivationCouponBatch.setUpdateTime(new Date());
                wechatActivationCouponBatchDao.updateTemplateById(wechatActivationCouponBatch);
            }
        }
    }
}
